﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VideoClub.Entities;

namespace VideoClub.DataAccess
{
    class DatosComunesDAO
    {
        public static List<TipoDocumento> ObtenerTodosLosTiposDocumentos()
        {
            List<TipoDocumento> list = new List<TipoDocumento>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT Id_Tipo_Doc ,Descripcion FROM Tipo_Doc ORDER BY Id_Tipo_Doc";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadTipoDoc(reader));
                }
            }

            return list;
        }

        private static TipoDocumento LoadTipoDoc(IDataReader reader)
        {
            try
            {
                TipoDocumento tipoDocumento = new TipoDocumento();

                tipoDocumento.pidTipoDocumento = Convert.ToInt32(reader["Id_Tipo_Doc"]);
                tipoDocumento.pdescripcion = Convert.ToString(reader["Descripcion"]);

                return tipoDocumento;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }

        }
        
        public static List<Barrio> ObtenerTodosLosBarrios()
        {
            List<Barrio> list = new List<Barrio>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT Id_Barrios ,Descripcion FROM Barrios ORDER BY Id_Barrios";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadBarrio(reader));
                }
            }

            return list;
        }

        private static Barrio LoadBarrio(SqlDataReader reader)
        {
            try
            {
                Barrio barrio = new Barrio();

                barrio.pidBarrio = Convert.ToInt32(reader["Id_Barrios"]);
                barrio.pnombre = Convert.ToString(reader["Descripcion"]);

                return barrio;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<Genero> ObtenerTodosLosGeneros()
        {
            List<Genero> list = new List<Genero>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT Id_Genero ,Descripcion FROM Genero ORDER BY Descripcion";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadGenero(reader));
                }
            }

            return list;
        }

        private static Genero LoadGenero(SqlDataReader reader)
        {

            try
            {
                Genero genero = new Genero();
                genero.pIdGenero = Convert.ToInt32(reader["Id_Genero"]);
                genero.pDescripcion = Convert.ToString(reader["Descripcion"]);
                return genero;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        internal static List<Multas> ObtenerTodosLosMultas()
        {
            List<Multas> list = new List<Multas>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT Id_Multas ,Descripcion, Importe FROM Multas ORDER BY Descripcion";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadMulta(reader));
                }
            }

            return list;
        }

        private static Multas LoadMulta(SqlDataReader reader)
        {
            try
            {
                Multas multas = new Multas();
                multas.IdMulta = Convert.ToInt32(reader["Id_Multas"]);
                multas.Descripcion = Convert.ToString(reader["Descripcion"]);
                multas.Importe = Convert.ToDecimal(reader["Importe"]);
                return multas;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<Formato> ObtenerTodosLosFormatos()
        {
            List<Formato> list = new List<Formato>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT * FROM formato ORDER BY DESCRIPCION";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadFormato(reader));
                }
            }

            return list;
        }

        private static Formato LoadFormato(SqlDataReader reader)
        {
            try
            {
                Formato formato = new Formato();
                formato.pIdFormato = Convert.ToInt32(reader["Id_Formato"]);
                formato.pDescripcion = Convert.ToString(reader["Descripcion"]);

                return formato;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }

        public static List<Director> ObtenerTodosLosDirectores()
        {
            List<Director> list = new List<Director>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT * FROM DIRECTOR ORDER BY NOMBRE";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadDirector(reader));
                }
            }

            return list;
        }

        private static Director LoadDirector(SqlDataReader reader)
        {
            try
            {
                Director director = new Director();
                director.pidDirector = Convert.ToInt32(reader["Id_Director"]);
                director.pNombre = Convert.ToString(reader["Nombre"]);
                director.pApellido = Convert.ToString(reader["Apellido"]);

                return director;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());

                throw;
            }
        }
    }
}
